package com.css.fxfzypg.modules.baseData.repository.impl;

import com.css.fxfzypg.modules.baseData.entity.BuildingVulnerability;
import com.css.fxfzypg.modules.baseData.queryParams.BuildingVulnerabilityQueryParams;
import com.css.fxfzypg.modules.baseData.repository.BuildingVulnerabilityRepository;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysAreaService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.StringUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.util.List;

@Slf4j
@Repository
public class BuildingVulnerabilityRepositoryImpl implements BuildingVulnerabilityRepository {
    @Resource
    JdbcTemplate jdbcTemplate;
    @Resource
    NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Autowired
    SysRoleService sysRoleService;

    @Autowired
    SUserService sUserService;

    @Autowired
    private SysAreaService sysAreaService;


    @Override
    public void save(BuildingVulnerability buildingVulnerability) {
        String sql = "insert into sys_buildingvulnerability_new( id,province,city,prefecture,structure_type," +
                "destroy,six,seven,eight,nine,ten,create_user,create_time,update_user,update_time,province_id,city_id," +
                "prefecture_id)" +
                " values(:id, :province, :city, :prefecture, :structureType, :destroy, :six, :seven, " +
                ":eight, :nine, :ten, :createUser, :createTime, :updateUser, :updateTime, :provinceId, :cityId, " +
                ":prefectureId)";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingVulnerability);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<BuildingVulnerability> getPage(BuildingVulnerabilityQueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability_new");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        StringBuilder orderSql = new StringBuilder(" order by prefecture,structure_type,destroy ");


        if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            //String province = sysAreaService.getAreaNameByAreaId(queryParams.getProvinceId());
            whereSql.append(" and province = '" + queryParams.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            //String city = sysAreaService.getAreaNameByAreaId(queryParams.getCityId());
            whereSql.append(" and city = '" + queryParams.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefecture())) {
            //String prefecture = sysAreaService.getAreaNameByAreaId(queryParams.getPrefectureId());
            whereSql.append(" and prefecture = '" + queryParams.getPrefecture() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStructureType())) {
            whereSql.append(" and structure_type = '" + queryParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            //String province = sysAreaService.getAreaNameByAreaId(queryParams.getProvinceId());
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            //String city = sysAreaService.getAreaNameByAreaId(queryParams.getCityId());
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            //String prefecture = sysAreaService.getAreaNameByAreaId(queryParams.getPrefectureId());
            whereSql.append(" and prefecture_id = '" + queryParams.getPrefectureId() + "'");
        }
        sql.append(whereSql).append(orderSql);

        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));

        return list;
    }

    @Override
    public List<BuildingVulnerability> getExportPage(BuildingVulnerabilityQueryParams queryParams) {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability_new");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        StringBuilder orderSql = new StringBuilder(" order by prefecture,structure_type,destroy ");


        if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            //String province = sysAreaService.getAreaNameByAreaId(queryParams.getProvinceId());
            whereSql.append(" and province = '" + queryParams.getProvince() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            //String city = sysAreaService.getAreaNameByAreaId(queryParams.getCityId());
            whereSql.append(" and city = '" + queryParams.getCity() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefecture())) {
            //String prefecture = sysAreaService.getAreaNameByAreaId(queryParams.getPrefectureId());
            whereSql.append(" and prefecture = '" + queryParams.getPrefecture() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getStructureType())) {
            whereSql.append(" and structure_type = '" + queryParams.getStructureType() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            //String province = sysAreaService.getAreaNameByAreaId(queryParams.getProvinceId());
            whereSql.append(" and province_id = '" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            //String city = sysAreaService.getAreaNameByAreaId(queryParams.getCityId());
            whereSql.append(" and city_id = '" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            //String prefecture = sysAreaService.getAreaNameByAreaId(queryParams.getPrefectureId());
            whereSql.append(" and prefecture_id = '" + queryParams.getPrefectureId() + "'");
        }
        sql.append(whereSql).append(orderSql);
        System.out.println(sql.toString());
        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));

        return list;
    }

    @Override
    public int delAllVulnerability(String province, String city, String prefecture, String structureType) {
        return jdbcTemplate.update("delete from sys_buildingvulnerability_new where province=? and city=? and " +
                " prefecture=? and structure_type=?", province, city, prefecture, structureType);
    }

    @Override
    public void delbuildingVulnerability(List<String> idList) {
        String sql = "delete from sys_buildingvulnerability_new where id in ('" + StringUtils.join(idList, "','") + "')";
        log.debug(sql);
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer countByCounty(String province, String city, String county) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_buildingvulnerability_new where prefecture = '" + county + "' and city = '" + city + "' and province = '" + province + "'", Integer.class);
    }

    @Override
    public Integer countByCity(String province, String city) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_buildingvulnerability_new where city = '" + city + "' and province = '" + province + "'", Integer.class);
    }

    @Override
    public Integer countByProvince(String province) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_buildingvulnerability_new where province = '" + province + "'", Integer.class);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from sys_buildingvulnerability where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?)", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }

    @Override
    public String getGeomByCode(String code) {
        String sql="select  coordinate FROM district_boundary  where code ='"+code+"'";
        return jdbcTemplate.queryForObject(sql,String.class);
    }

    @Override
    public void update(BuildingVulnerability buildingVulnerability) {
        String sql = "update sys_buildingvulnerability_new set six = :six, seven= :seven ,eight = :eight ,nine = :nine " +
                ",ten = :ten ,update_user = :updateUser ,update_time = :updateTime ,province_id = :provinceId ," +
                "city_id = :cityId ,prefecture_id = :prefectureId where id = :id";
        SqlParameterSource source = new BeanPropertySqlParameterSource(buildingVulnerability);
        namedParameterJdbcTemplate.update(sql, source);
    }

    @Override
    public List<BuildingVulnerability> isHaveRepeatData(String province, String city, String prefecture, String structureType) {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability_new");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(province)) {
            whereSql.append(" and province = '" + province + "'");
        }
        if (!PlatformObjectUtils.isEmpty(city)) {
            whereSql.append(" and city = '" + city + "'");
        }
        if (!PlatformObjectUtils.isEmpty(prefecture)) {
            whereSql.append(" and prefecture = '" + prefecture + "'");
        }
        if (!PlatformObjectUtils.isEmpty(structureType)) {
            whereSql.append(" and structure_type = '" + structureType + "'");
        }
        sql.append(whereSql);

        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));

        return list;
    }

    @Override
    public List<BuildingVulnerability> getVulnerability() {
        StringBuilder sql = new StringBuilder("select * from sys_buildingvulnerability_new order by province,city," +
                "prefecture");
        List<BuildingVulnerability> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(BuildingVulnerability.class));
        return list;
    }

    @Override
    public List<BuildingVulnerability> getAllVulnerability(String province, String city, String prefecture,
                                                           String tab, String source) {
        //String sql=" select * from sys_buildingvulnerability_new where province= ? and city= ? and prefecture= ?";
        String sql = " select * from sys_buildingvulnerability_new where province= ? and city= ? and prefecture= ?";
        if (StringUtils.isNotBlank(source)) {
            sql += " and structure_type in (" + source + ")";
        }
        if ("1".equals(tab)) {
            sql = sql + " order by destroy,structure_type";
        } else if ("2".equals(tab)) {
            sql = sql + " order by structure_type,destroy";
        }
        return jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(BuildingVulnerability.class),
                province, city, prefecture);
    }
}
